pyodbcでBigQueryへ接続してみる

kobayashi.m

2022.04.18

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データアナリティクス事業本部のkobayashiです。

Pythonで各種データベースの操作を行うpyodbcでBigQueryへ接続してみましたのでその内容をまとめます。途中、文字コードの扱いでハマってしまったので注意点としてその点もまとめます。

環境

macOS 10.15.7
Python 3.7.12
pyodbc 4.0.32

BigQueryへの接続

今回の目的はpyodbcを使ってBigQueryからデータを取得することとしています。取得したいデータは以下のようなデータを扱ってみたいと思います。

date	month	city	w_type	temperature	sunlight	cloudage
2021-12-13	12	名古屋	晴	10.8	9.7	3
2021-11-12	11	仙台	晴	11.3	8.1	1.8
2021-11-13	11	大阪	晴	12.1	8.8	5
2021-11-11	11	札幌	晴	7.8	1.7	7.5
2021-11-13	11	長野	晴	7.3	9.3	NULL
2021-11-13	11	京都	晴	10.5	7.6	NULL
2021-11-17	11	東京	晴	13.1	9.4	0.5
2021-10-13	10	横浜	晴	13.6	10.1	NULL
2021-03-03	3	京都	晴	5.9	6.6	NULL
2021-03-03	3	那覇	晴	18.4	8.5	4.3
2021-02-03	2	大阪	晴	6.8	8.2	5
2021-03-03	3	名古屋	晴	7.3	10.7	1
2021-01-01	1	横浜	晴	7.7	11	NULL
2021-04-12	4	仙台	晴	3.1	9.7	4.3

接続設定の手順は公式ドキュメント(Connecting to Google BigQuery · mkleehammer/pyodbc Wiki · GitHub )に記載がありますのでこの内容で進めますが、macOS用の設定は無いのでLinuxの設定を一部参考に行いました。

ドライバファイル・接続情報の設定

はじめにドライバをインストールします。インストールは公式ドキュメント(Connecting to Google BigQuery · mkleehammer/pyodbc Wiki · GitHub )にリンクがありますので BigQuery 用の ODBC ドライバと JDBC ドライバ | Google Cloud から「macOS（dmg）」をダウンロードします。

ダウンロードが完了したらdmgファイルをダブルクリックしてインストールをします。その際にインストール先は特に変更せず進めます。

インストールが完了すると/Library/ODBC/odbcinst.iniにBigQuery用のドライバファイル情報が追記されますが、後の工程で使うのでメモしておきます。

[Simba GoogleBigQuery ODBC Driver]
Driver = /Library/simba/googlebigqueryodbc/lib/libgooglebigqueryodbc_sbu.dylib

次に pyodbcが利用するドライバの設定ファイルが/usr/local/etc/odbcinst.iniにあるのでここに先程メモしたドライバファイルの情報を追記します。

....

-- 以下を追記
[Simba GoogleBigQuery ODBC Driver]
Driver=/Library/simba/googlebigqueryodbc/lib/libgooglebigqueryodbc_sbu.dylib

最後に/usr/local/etc/odbc.iniへBigQueryへの接続情報を追記します。

[BigQueryDev]
Driver=Simba GoogleBigQuery ODBC Driver
Catalog={プロジェクトID}
OAuthMechanism=0
Email={サービスアカウントe-mail}
KeyFilePath={サービスアカウント用キーファイルのパス}

プロジェクトIDやサービスアカウントなど別の接続情報を使いたい場合はこのブロックを増やす必要があります。

[BigQueryDev]
Driver=Simba GoogleBigQuery ODBC Driver
Catalog={プロジェクトID}
OAuthMechanism=0
Email={サービスアカウントe-mail}
KeyFilePath={サービスアカウント用キーファイルのパス}

[BigQueryStg]
Driver=Simba GoogleBigQuery ODBC Driver
Catalog={プロジェクトID 2}
OAuthMechanism=0
Email={サービスアカウントe-mail 2}
KeyFilePath={サービスアカウント用キーファイルのパス}

これで接続設定は完了しましたのでpyodbcを使ってデータを取得してみたいと思います。

pyodbcを使ってBigQueryのデータを取得

pyodbcで一般的にデータベースからデータを取得するには以下の流れで行います。

Connectionを確立
cursorを定義する
クエリをexecuteする
fetchしてデータを取り出す

スクリプトは以下のようになります。

import pyodbc

DSN = "BigQueryDev"

conn = pyodbc.connect("DSN={}".format(DSN), autocommit=True)
cursor = conn.cursor()
with conn:
    cursor.execute('select * from data_set_test1.jp_weather;')
    print(cursor.description)
    data = cursor.fetchall()
    for _data in data:
        print(_data)

pyodbc.connectの引数としてドライバファイルで設定した接続情報をDSNとして設定するだけです。

文字コードの変更

上記のコードを実行すればBigQueryからデータが取得できるのですが、おそらく以下のエラーが出ます。

pyodbc.ProgrammingError: ('42000', '[42000] [Simba][BigQuery] (70) Invalid query: Syntax error: Illegal input character "\\357" at [1:1] (70) (SQLExecDirectW)')

エラー内容から文字コードに起因するエラーだと判断できたので調べてみるとMySQLですが同じ様な問題の解決方法が見つかりました。

pyodbc doesn't correctly deal with unicode data - Stack Overflow

If you are using UTF-8, most of the lines will be similar to other databases, but unfortunately the metadata on macOS (this may not be necessary on Linux) will return metadata (column names) in UTF-32LE.

デコードとエンコードの設定を追加する必要があるとのことなのでこれを参考に上記のコードを以下の用に修正します。

import pyodbc

DSN = "BigQueryDev"

conn = pyodbc.connect("DSN={}".format(DSN), autocommit=True)
conn.setdecoding(pyodbc.SQL_CHAR, encoding="utf-8")
conn.setdecoding(pyodbc.SQL_WCHAR, encoding="utf-8")
conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
conn.setencoding(encoding="utf8")
cursor = conn.cursor()
with conn:
    cursor.execute('select * from data_set_test1.jp_weather;')
    print(cursor.description)
    data = cursor.fetchall()
    for _data in data:
        print(_data)

再度スクリプトを実行してみるとうまくデータが取得できることがわかります。

(datetime.date(2021, 11, 13), 11, '名古屋', '晴', 10.8, 0.0, 9.7, 3.0)
(datetime.date(2021, 11, 13), 11, '仙台', '晴', 11.3, 0.0, 8.1, 1.8)
(datetime.date(2021, 11, 13), 11, '大阪', '晴', 12.1, 0.0, 8.8, 5.0)
(datetime.date(2021, 11, 13), 11, '札幌', '晴', 7.8, 0.0, 1.7, 7.5)
(datetime.date(2021, 11, 13), 11, '長野', '晴', 7.3, 0.0, 9.3, None)
(datetime.date(2021, 11, 13), 11, '京都', '晴', 10.5, 0.0, 7.6, None)
(datetime.date(2021, 11, 13), 11, '東京', '晴', 13.1, 0.0, 9.4, 0.5)
(datetime.date(2021, 11, 13), 11, '横浜', '晴', 13.6, 0.0, 10.1, None)

余談ですが、pyodbcのコネクションを渡せばpandas.read_sqlでSQLの実行結果をDataFrameで取得できます。

import pandas as pd
import pyodbc

DSN = "BigQueryDev"

conn = pyodbc.connect("DSN={}".format(DSN), autocommit=True)
conn.setdecoding(pyodbc.SQL_CHAR, encoding="utf-8")
conn.setdecoding(pyodbc.SQL_WCHAR, encoding="utf-8")
conn.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
conn.setencoding(encoding="utf8")
cursor = conn.cursor()
with conn:
    data = pd.read_sql("select * from data_set_test1.jp_weather;", conn)
    print(data)

            date  month city  ... precipitation  sunlight  cloudage
0     2021-11-13     11  名古屋  ...           0.0       9.7       3.0
1     2021-11-13     11   仙台  ...           0.0       8.1       1.8
2     2021-11-13     11   大阪  ...           0.0       8.8       5.0
3     2021-11-13     11   札幌  ...           0.0       1.7       7.5
4     2021-11-13     11   長野  ...           0.0       9.3       NaN
...          ...    ...  ...  ...           ...       ...       ...
3655  2022-02-15      2   札幌  ...           0.5       3.9       7.5
3656  2022-02-15      2   仙台  ...           8.0       0.0      10.0
3657  2022-02-18      2   長野  ...           6.5       1.0       NaN
3658  2022-02-20      2   長野  ...           2.5       2.7       NaN
3659  2021-02-27      2   札幌  ...           5.0       0.8       9.8